"""
SQL Statements and Expressions API
http://docs.sqlalchemy.org/en/latest/core/sqlelement.html

"""
from sqlalchemy import create_engine
from sqlalchemy import select, between
from sqlalchemy import Table, Column, Integer, String, MetaData

engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', echo=True)
metadata = MetaData()
users_table = Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String(50)),
              Column('fullname', String(50)),
              mysql_engine='InnoDB',
              mysql_charset='utf8'
              )

def ex1():
    from sqlalchemy import and_

    stmt = select([users_table]).where(
        and_(
            users_table.c.name == 'wendy',
            users_table.c.fullname == 'wendy'
        )
    )
    print(stmt)

    stmt = select([users_table]).where(between(users_table.c.id, 5, 7))
    print(stmt)

def ex2():
    from sqlalchemy import bindparam

    stmt = select([users_table]). \
        where(users_table.c.name == bindparam('username'))
    print(stmt)
    conn = engine.connect()
    result = conn.execute(stmt, username='wendy')
    print(result)
    conn.close()


if __name__ == '__main__':
    ex2()